import java.sql.*;
import java.util.*;

import org.hsqldb.*;
import controller.*;

/**
 * Application class with starter method public static void main()
 * TODO: revise for segregating code used for testing purposes, perhaps a new class for handling HSQL 'servers'
 */
public class DBI {
	static private Server hsqlServer = null;
	static private Connection connection = null;
	static private Map<String, RDBMS> databases = new HashMap<String, RDBMS>();
	
	public static void main(String[] args) throws Exception {
		try {
			setupServer();
			
			//Methods in case the database has become corrupted or otherwise inconsistent
			//dropHSQLDatabaseStructure();
			//createHSQLDatabaseStructure();
			//insertAcademicDatabaseIntoHSQLDatabase();
			
			ResultSet rs = connection.prepareStatement("SELECT * FROM DB;").executeQuery();
			while(rs.next()) {
				try {
					String databaseName = rs.getString("NAME");
					System.out.println("LOADING DATABASE \""+databaseName+"\"");
					
					RDBMS database = new RDBMS(databaseName);
					databases.put(databaseName, database);
					
					//database.loadDatabaseNative(connection); 	//deprecated
					//database.loadDatabase(connection);		//standard way to load a database
					database.loadAcademicDatabase(connection);	//loading the specific academic database
					database.fillAcademicDatabase(connection);	//filling the specific academic database
					
					System.out.println("Printing database metadata: ");
					database.printAllMetadata();
					System.out.println("");
					
					System.out.println("Printing database contents:");
					database.printAllData();
					System.out.println("");
					
					System.out.println("Printing database index contents:");
					database.printAllIndexes();
					System.out.println("");
					
					System.out.println("");
				} catch (Exception e) {
					System.out.println("Cannot load database \""+rs.getString("NAME")+"\" , "+
							"if name has \"_TEMP\" suffix, database storage has potentially become inconsistent");
				}
			}
            
            System.out.println("Press the any key to shut down the server.");
			System.in.read();
			
			System.out.println("Saving databases metadata");
			for(String databaseName : databases.keySet()) {
				System.out.println("Database: "+databaseName);
				RDBMS database = databases.get(databaseName);
				database.saveDatabase(connection);
				System.out.println("");
			}
			System.out.println("Done saving metadata");
			
			try { //sometimes results in a EOF exception, not a problem really
				connection.prepareStatement("SHUTDOWN;").execute(); 
			} catch (Exception e) {}
            hsqlServer.shutdown(); //only necessary with server, not for mem or file 
		} finally {
			if (hsqlServer != null) {
				hsqlServer.stop();
			}
			if(connection != null) {
				connection.close();
			}
		}
	}
	
	private static void setupServer() throws ClassNotFoundException, SQLException {
		hsqlServer = new Server();
		hsqlServer.setLogWriter(null);
		hsqlServer.setSilent(true);
		hsqlServer.setDatabaseName(0, "DBIdb");
		hsqlServer.setDatabasePath(0, "file:DBIdb");
		hsqlServer.start();
		
		Class.forName("org.hsqldb.jdbcDriver");
		
		connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/DBIdb", "sa", "");
//		other options instead of server, hsqlServer object not needed with these other options
//		connection = DriverManager.getConnection("jdbc:hsqldb:mem:DBIdb", "sa", "");
//		connection = DriverManager.getConnection("jdbc:hsqldb:file:DBIdb", "sa", "");
		connection.setAutoCommit(true);
	}
	
	//Private static methods for testing purposes of the database program
	private static void dropHSQLDatabaseStructure() throws SQLException {
		connection.prepareStatement("DROP TABLE fkeecol IF EXISTS;"	).execute();
		connection.prepareStatement("DROP TABLE fkee IF EXISTS;"	).execute();
		connection.prepareStatement("DROP TABLE pkee IF EXISTS;"	).execute();
		connection.prepareStatement("DROP TABLE keecol IF EXISTS;"	).execute();
		connection.prepareStatement("DROP TABLE kee IF EXISTS;"		).execute();
		connection.prepareStatement("DROP TABLE col IF EXISTS;"		).execute();
		connection.prepareStatement("DROP TABLE tbl IF EXISTS;"		).execute();
		connection.prepareStatement("DROP TABLE db IF EXISTS;"		).execute();
	}
	
	private static void createHSQLDatabaseStructure() throws SQLException {
		connection.prepareStatement(
			"CREATE TABLE DB (" +
				"name VARCHAR(255)," +
				"PRIMARY KEY(name)" +
			");").execute();
		
		connection.prepareStatement(
			"CREATE TABLE TBL (" +
				"dbname VARCHAR(255)," +
				"name VARCHAR(255)," +
				"PRIMARY KEY(dbname, name)," +
				"FOREIGN KEY(dbname) REFERENCES DB(name)" +
			");").execute();
		
		connection.prepareStatement(
			"CREATE TABLE COL (" +
				"dbname VARCHAR(255)," +
				"tblname VARCHAR(255)," +
				"name VARCHAR(255)," +
				"datatype VARCHAR(3)," +
				"notnull BOOLEAN DEFAULT false," +
				"PRIMARY KEY(dbname, tblname, name)," +
				"FOREIGN KEY(dbname, tblname) REFERENCES TBL(dbname, name)," +
			");").execute();
		
		connection.prepareStatement(
			"CREATE TABLE KEE (" +
				"dbname VARCHAR(255)," +
				"tblname VARCHAR(255)," +
				"name VARCHAR(255)," +
				"PRIMARY KEY(dbname, name)," +
				"FOREIGN KEY(dbname, tblname) REFERENCES TBL(dbname, name)," +
				"UNIQUE(dbname, tblname, name)" +
			");").execute();
		
		connection.prepareStatement(
			"CREATE TABLE KEECOL (" +
				"dbname VARCHAR(255)," +
				"tblname VARCHAR(255)," +
				"colname VARCHAR(255)," +
				"keename VARCHAR(255)," +
				"seqnr INTEGER DEFAULT 0," +
				"PRIMARY KEY(dbname, tblname, colname, keename)," +
				"FOREIGN KEY(dbname, tblname, colname) REFERENCES COL(dbname, tblname, name)," +
				"FOREIGN KEY(dbname, tblname, keename) REFERENCES KEE(dbname, tblname, name)," +
				"UNIQUE (dbname, keename, seqnr)" +
			");").execute();
		
		connection.prepareStatement(
			"CREATE TABLE PKEE (" +
				"dbname VARCHAR(255)," +
				"tblname VARCHAR(255)," +
				"keename VARCHAR(255)," +
				"PRIMARY KEY(dbname, tblname)," +
				"FOREIGN KEY(dbname, tblname, keename) REFERENCES KEE(dbname, tblname, name)" +
			");").execute();
		
		connection.prepareStatement(
			"CREATE TABLE FKEE (" +
				"dbname VARCHAR(255)," +
				"tblname VARCHAR(255)," +
				"name VARCHAR(255)," +
				"tokeename VARCHAR(255)," +
				"PRIMARY KEY(dbname, name)," +
				"FOREIGN KEY(dbname, tblname) REFERENCES TBL(dbname, name)," +
				"FOREIGN KEY(dbname, tokeename) REFERENCES KEE(dbname, name)," +
				"UNIQUE(dbname, tblname, name)" +
			");").execute();
		
		connection.prepareStatement(
			"CREATE TABLE FKEECOL (" +
				"dbname VARCHAR(255)," +
				"tblname VARCHAR(255)," +
				"colname VARCHAR(255)," +
				"fkeename VARCHAR(255)," +
				"seqnr INTEGER DEFAULT 0," +
				"PRIMARY KEY(dbname, tblname, colname, fkeename)," +
				"FOREIGN KEY(dbname, tblname, colname) REFERENCES COL(dbname, tblname, name)," +
				"FOREIGN KEY(dbname, tblname, fkeename) REFERENCES FKEE(dbname, tblname, name)," +
				"UNIQUE (dbname, fkeename, seqnr)" +
			");	").execute();
	}
	
	private static void insertAcademicDatabaseIntoHSQLDatabase() throws SQLException {
		connection.prepareStatement(
			"INSERT INTO DB(name)" +
				"VALUES" +
				"('DB')" +
			";").execute();
		
		connection.prepareStatement(
			"INSERT INTO TBL(dbname, name)" +
				"VALUES" +
				"('DB',	'DB')," +
				"('DB',	'TBL')," +
				"('DB',	'COL')," +
				"('DB',	'KEE')," +
				"('DB',	'KEECOL')," +
				"('DB',	'PKEE')," +
				"('DB',	'FKEE')," +
				"('DB',	'FKEECOL')" +
			";").execute();
		
		connection.prepareStatement(
			"INSERT INTO COL(dbname, tblname, name, datatype, notnull)" +
				"VALUES" +
				"('DB',	'DB',		'name',			'STR',	true)," +
				"('DB',	'TBL',		'dbname',		'STR',	true)," +
				"('DB',	'TBL',		'name',			'STR',	true)," +
				"('DB',	'COL',		'dbname',		'STR',	true)," +
				"('DB',	'COL',		'tblname',		'STR',	true)," +
				"('DB',	'COL',		'name',			'STR',	true)," +
				"('DB',	'COL',		'datatype',		'STR',	true)," +
				"('DB',	'COL',		'notnull',		'BLN',	false)," +
				"('DB',	'KEE',		'dbname',		'STR',	true)," +
				"('DB',	'KEE',		'tblname',		'STR',	true)," +
				"('DB',	'KEE',		'name',			'STR',	true)," +
				"('DB',	'KEECOL',	'dbname',		'STR',	true)," +
				"('DB',	'KEECOL',	'tblname',		'STR',	true)," +
				"('DB',	'KEECOL',	'colname',		'STR',	true)," +
				"('DB',	'KEECOL',	'keename',		'STR',	true)," +
				"('DB',	'KEECOL',	'seqnr',		'INT',	true)," +
				"('DB',	'PKEE',		'dbname',		'STR',	true)," +
				"('DB',	'PKEE',		'tblname',		'STR',	true)," +
				"('DB',	'PKEE',		'keename',		'STR',	true)," +
				"('DB',	'FKEE',		'dbname',		'STR',	true)," +
				"('DB',	'FKEE',		'tblname',		'STR',	true)," +
				"('DB',	'FKEE',		'name',			'STR',	true)," +
				"('DB',	'FKEE',		'tokeename',	'STR',	true)," +
				"('DB',	'FKEECOL',	'dbname',		'STR',	true)," +
				"('DB',	'FKEECOL',	'tblname',		'STR',	true)," +
				"('DB',	'FKEECOL',	'colname',		'STR',	true)," +
				"('DB',	'FKEECOL',	'fkeename',		'STR',	true)," +
				"('DB',	'FKEECOL',	'seqnr',		'INT',	true)" +
			";").execute();
		
		connection.prepareStatement(
			"INSERT INTO KEE(dbname, tblname, name)" +
				"VALUES" +
				"('DB',	'DB',		'DB_PKEE')," +
				"('DB',	'TBL',		'TBL_PKEE')," +
				"('DB',	'COL',		'COL_PKEE')," +
				"('DB',	'KEE',		'KEE_PKEE')," +
				"('DB',	'KEE',		'KEE_UKEE1')," +
				"('DB',	'KEECOL',	'KEECOL_PKEE')," +
				"('DB',	'KEECOL',	'KEECOL_UKEE1')," +
				"('DB',	'PKEE',		'PKEE_PKEE')," +
				"('DB',	'FKEE',		'FKEE_PKEE')," +
				"('DB',	'FKEE',		'FKEE_UKEE1')," +
				"('DB',	'FKEECOL',	'FKEECOL_PKEE')," +
				"('DB',	'FKEECOL',	'FKEECOL_UKEE1')" +
			";").execute();
		
		connection.prepareStatement(
			"INSERT INTO KEECOL(dbname, tblname, colname, keename, seqnr)" +
				"VALUES" +
				"('DB',	'DB',		'name',		'DB_PKEE',			0)," +
				"('DB',	'TBL',		'dbname',	'TBL_PKEE',			0)," +
				"('DB',	'TBL',		'name',		'TBL_PKEE',			1)," +
				"('DB',	'COL',		'dbname',	'COL_PKEE',			0)," +
				"('DB',	'COL',		'tblname',	'COL_PKEE',			1)," +
				"('DB',	'COL',		'name',		'COL_PKEE',			2)," +
				"('DB',	'KEE',		'dbname',	'KEE_PKEE',			0)," +
				"('DB',	'KEE',		'name',		'KEE_PKEE',			1)," +
				"('DB',	'KEE',		'dbname',	'KEE_UKEE1',		0)," +
				"('DB',	'KEE',		'tblname',	'KEE_UKEE1',		1)," +
				"('DB',	'KEE',		'name',		'KEE_UKEE1',		2)," +
				"('DB',	'KEECOL',	'dbname',	'KEECOL_PKEE',		0)," +
				"('DB',	'KEECOL',	'tblname',	'KEECOL_PKEE',		1)," +
				"('DB',	'KEECOL',	'colname',	'KEECOL_PKEE',		2)," +
				"('DB',	'KEECOL',	'keename',	'KEECOL_PKEE',		3)," +
				"('DB',	'KEECOL',	'dbname',	'KEECOL_UKEE1',		0)," +
				"('DB',	'KEECOL',	'keename',	'KEECOL_UKEE1',		1)," +
				"('DB',	'KEECOL',	'seqnr',	'KEECOL_UKEE1',		2)," +
				"('DB',	'PKEE',		'dbname',	'PKEE_PKEE',		0)," +
				"('DB',	'PKEE',		'tblname',	'PKEE_PKEE',		1)," +
				"('DB',	'FKEE',		'dbname',	'FKEE_PKEE',		0)," +
				"('DB',	'FKEE',		'tblname',	'FKEE_PKEE',		1)," +
				"('DB',	'FKEE',		'dbname',	'FKEE_UKEE1',		0)," +
				"('DB',	'FKEE',		'tblname',	'FKEE_UKEE1',		1)," +
				"('DB',	'FKEE',		'name',		'FKEE_UKEE1',		2)," +
				"('DB',	'FKEECOL',	'dbname',	'FKEECOL_PKEE',		0)," +
				"('DB',	'FKEECOL',	'tblname',	'FKEECOL_PKEE',		1)," +
				"('DB',	'FKEECOL',	'colname',	'FKEECOL_PKEE',		2)," +
				"('DB',	'FKEECOL',	'fkeename',	'FKEECOL_PKEE',		3)," +
				"('DB',	'FKEECOL',	'dbname',	'FKEECOL_UKEE1',	0)," +
				"('DB',	'FKEECOL',	'fkeename',	'FKEECOL_UKEE1',	1)," +
				"('DB',	'FKEECOL',	'seqnr',	'FKEECOL_UKEE1',	2)" +
			";").execute();
		
		connection.prepareStatement(
			"INSERT INTO PKEE(dbname, tblname, keename)" +
				"VALUES" +
				"('DB',	'DB',		'DB_PKEE')," +
				"('DB',	'TBL',		'TBL_PKEE')," +
				"('DB',	'COL',		'COL_PKEE')," +
				"('DB',	'KEE',		'KEE_PKEE')," +
				"('DB',	'KEECOL',	'KEECOL_PKEE')," +
				"('DB',	'PKEE',		'PKEE_PKEE')," +
				"('DB',	'FKEE',		'FKEE_PKEE')," +
				"('DB',	'FKEECOL',	'FKEECOL_PKEE')" +
			";").execute();
		
		connection.prepareStatement(
			"INSERT INTO FKEE(dbname, tblname, name, tokeename)" +
				"VALUES" +
				"	('DB',	'TBL',		'TBL_FKEE__DB_PKEE',		'DB_PKEE')," +
				"	('DB',	'COL',		'COL_FKEE__TBL_PKEE',		'TBL_PKEE')," +
				"	('DB',	'KEE',		'KEE_FKEE__TBL_PKEE',		'TBL_PKEE')," +
				"	('DB',	'KEECOL',	'KEECOL_FKEE__COL_PKEE',	'COL_PKEE')," +
				"	('DB',	'KEECOL',	'KEECOL_FKEE__KEE_UKEE1',	'KEE_UKEE1')," +
				"	('DB',	'PKEE',		'PKEE_FKEE__KEE_UKEE1',		'KEE_UKEE1')," +
				"	('DB',	'FKEE',		'FKEE_FKEE__TBL_PKEE',		'TBL_PKEE')," +
				"	('DB',	'FKEE',		'FKEE_FKEE__KEE_PKEE',		'KEE_PKEE')," +
				"	('DB',	'FKEECOL',	'FKEECOL_FKEE__COL_PKEE',	'COL_PKEE')," +
				"	('DB',	'FKEECOL',	'FKEECOL_FKEE__FKEE_UKEE1',	'FKEE_UKEE1')" +
			";").execute();
		
		connection.prepareStatement(
			"INSERT INTO FKEECOL(dbname, tblname, colname, fkeename, seqnr)" +
				"VALUES" +
				"('DB',	'TBL',		'dbname',		'TBL_FKEE__DB_PKEE',		'0')," +
				"('DB',	'COL',		'dbname',		'COL_FKEE__TBL_PKEE',		'0')," +
				"('DB',	'COL',		'tblname',		'COL_FKEE__TBL_PKEE',		'1')," +
				"('DB',	'KEE',		'dbname',		'KEE_FKEE__TBL_PKEE',		'0')," +
				"('DB',	'KEE',		'tblname',		'KEE_FKEE__TBL_PKEE',		'1')," +
				"('DB',	'KEECOL',	'dbname',		'KEECOL_FKEE__COL_PKEE',	'0')," +
				"('DB',	'KEECOL',	'tblname',		'KEECOL_FKEE__COL_PKEE',	'1')," +
				"('DB',	'KEECOL',	'colname',		'KEECOL_FKEE__COL_PKEE',	'2')," +
				"('DB',	'KEECOL',	'dbname',		'KEECOL_FKEE__KEE_UKEE1',	'0')," +
				"('DB',	'KEECOL',	'tblname',		'KEECOL_FKEE__KEE_UKEE1',	'1')," +
				"('DB',	'KEECOL',	'keename',		'KEECOL_FKEE__KEE_UKEE1',	'2')," +
				"('DB',	'PKEE',		'dbname',		'PKEE_FKEE__KEE_UKEE1',		'0')," +
				"('DB',	'PKEE',		'tblname',		'PKEE_FKEE__KEE_UKEE1',		'1')," +
				"('DB',	'PKEE',		'keename',		'PKEE_FKEE__KEE_UKEE1',		'2')," +
				"('DB',	'FKEE',		'dbname',		'FKEE_FKEE__TBL_PKEE',		'0')," +
				"('DB',	'FKEE',		'tblname',		'FKEE_FKEE__TBL_PKEE',		'1')," +
				"('DB',	'FKEE',		'dbname',		'FKEE_FKEE__KEE_PKEE',		'0')," +
				"('DB',	'FKEE',		'tokeename',	'FKEE_FKEE__KEE_PKEE',		'1')," +
				"('DB',	'FKEECOL',	'dbname',		'FKEECOL_FKEE__COL_PKEE',	'0')," +
				"('DB',	'FKEECOL',	'tblname',		'FKEECOL_FKEE__COL_PKEE',	'1')," +
				"('DB',	'FKEECOL',	'colname',		'FKEECOL_FKEE__COL_PKEE',	'2')," +
				"('DB',	'FKEECOL',	'dbname',		'FKEECOL_FKEE__FKEE_UKEE1',	'0')," +
				"('DB',	'FKEECOL',	'tblname',		'FKEECOL_FKEE__FKEE_UKEE1',	'1')," +
				"('DB',	'FKEECOL',	'fkeename',		'FKEECOL_FKEE__FKEE_UKEE1',	'2')" +
			";").execute();
	}
}
